package com.css.fxfzypg.manage.sampling.repository.impl;

import com.css.fxfzypg.base.dict.repository.entity.DictEntity;
import com.css.fxfzypg.manage.sampling.repository.SamplingReqository;
import com.css.fxfzypg.manage.sampling.repository.entity.SamplingEntity;
import com.css.fxfzypg.manage.sampling.repository.entity.StructureFeature;
import com.css.fxfzypg.manage.sampling.vo.SamplingParams;
import com.css.fxfzypg.manage.sampling.vo.SamplingSurveyPointVo;
import com.css.fxfzypg.util.PlatformObjectUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;

@Slf4j
@Repository
public class SamplingReqositoryImpl implements SamplingReqository {

    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    @Transactional
    public boolean insertSurvey(SamplingEntity samplingEntity) {
        String sql = "INSERT INTO ypg_sampling_survey_new(id, task_name, task_code, field_survey_name, longitude, latitude, province, province_id, city, city_id, area, area_id, township, township_id, assignor_name, assignor_id, total_household_num, total_population, actual_resident_population, migrant_workers, children_proportion, young_proportion, old_age_proportion, per_capita_income, main_economic_sources, traffic_conditions, environment, house_num, room_average_num, main_building_materials, high_structure, steel_structure, concrete_structure, masonry_structure, brick_wood_structure, soil_structure, wood_structure, stone_structure, other_structure, build70s_before, build70s, build80s, build90s, build00s, build10s, build20s, is_school, student_num, classroom_num, is_hospital, doctor_num, bed_num, is_disaster, disaster_type, disaster_scale, is_reservoir, dam_type, reservoir_capacity, remarks, is_submit, del_flag, create_name, create_id, create_time, submitter, submitter_id, submit_time, geom, high_feature, steel_feature, concrete_feature, masonry_feature, brick_wood_feature, soil_feature, wood_feature, stone_feature, other_feature, type_pie_id, years_pie_id, build16s) values(:id, :taskName, :taskCode, :fieldSurveyName, :longitude, :latitude, :province, :provinceId, :city, :cityId, :area, :areaId, :township, :townshipId, :assignorName, :assignorId, :totalHouseholdNum, :totalPopulation, :actualResidentPopulation, :migrantWorkers, :childrenProportion, :youngProportion, :oldAgeProportion, :perCapitaIncome, :mainEconomicSources, :trafficConditions, :environment, :houseNum, :roomAverageNum, :mainBuildingMaterials, :highStructure, :steelStructure, :concreteStructure, :masonryStructure, :brickWoodStructure, :soilStructure, :woodStructure, :stoneStructure, :otherStructure, :build70sBefore, :build70s, :build80s, :build90s, :build00s, :build10s, :build20s, :isSchool, :studentNum, :classroomNum, :isHospital, :doctorNum, :bedNum, :isDisaster, :disasterType, :disasterScale, :isReservoir, :damType, :reservoirCapacity, :remarks, :isSubmit, :delFlag, :createName, :createId, :createTime, :submitter, :submitterId, :submitTime, ST_GeometryFromText(:geom, 4490), :highFeature, :steelFeature, :concreteFeature, :masonryFeature, :brickWoodFeature, :soilFeature, :woodFeature, :stoneFeature, :otherFeature, :typePieId, :yearsPieId, :build16s)";
//        String sql = "insert into ypg_sampling_survey_new values(:id,:taskName,:taskCode,:fieldSurveyName,:longitude,:latitude,:province,:provinceId,:city,:cityId,:area,:areaId,:township,:townshipId,:assignorName,:assignorId,:totalHouseholdNum,:totalPopulation,:actualResidentPopulation,:migrantWorkers,:childrenProportion,:youngProportion,:oldAgeProportion,:perCapitaIncome,:mainEconomicSources,:trafficConditions,:environment,:houseNum,:roomAverageNum,:mainBuildingMaterials,:highStructure,:steelStructure,:concreteStructure,:masonryStructure,:brickWoodStructure,:soilStructure,:woodStructure,:stoneStructure,:otherStructure,:build70sBefore,:build70s,:build80s,:build90s,:build00s,:build10s,:build20s,:isSchool,:studentNum,:classroomNum,:isHospital,:doctorNum,:bedNum,:isDisaster,:disasterType,:disasterScale,:isReservoir,:damType,:reservoirCapacity,:remarks,:isSubmit,:delFlag,:createName,:createId,:createTime,:submitter,:submitterId,:submitTime, ST_GeometryFromText(:geom,4490))";
        SqlParameterSource source = new BeanPropertySqlParameterSource(samplingEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }


    @Override
    public Map<String, Object> querySubmitOrNot(String submitFlag, String taskCode, int curPage, int pageSize, String userId) {
        try {
            StringBuilder sql = new StringBuilder("select DISTINCT * from ypg_sampling_survey_new where del_flag = '0' and task_code = '" + taskCode + "' and is_submit = '" + submitFlag + "' ");
            if (submitFlag.equals("0")) {
                sql.append(" and (assignor_id = '' or assignor_id is null or assignor_id='" + userId + "') order by " +
                        "create_time desc");
            } else {
                sql.append(" and assignor_id='" + userId + "' order by submit_time desc");
            }
            int firstIndex = (curPage - 1) * pageSize;
            int lastIndex = pageSize;
            StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingEntity.class)).size();
            List<SamplingEntity> samplingEntityList = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SamplingEntity.class));
            Map<String, Object> map = new HashMap();
            map.put("total", total);
            map.put("rows", samplingEntityList);
            return map;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public boolean updateIsSubmit(Map<String, String> map) {
        String sql = " update ypg_sampling_survey_new set is_submit = '1', submitter = ?, submitter_id = ?, submit_time = ? where id = ?";
        return jdbcTemplate.update(sql, map.get("userName"), map.get("userId"), new Date(), map.get("id")) > 0;
    }

    @Override
    public SamplingEntity queryData(String id) {
        String sql = "select * from ypg_sampling_survey_new where del_flag = '0' and id = ?";
        try {
            SamplingEntity samplingEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SamplingEntity.class), id);
            return samplingEntity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public boolean modifyData(SamplingEntity samplingEntity) {
        String sql = "update ypg_sampling_survey_new set assignor_name=:assignorName,assignor_id=:assignorId,total_household_num=:totalHouseholdNum,total_population=:totalPopulation,actual_resident_population=:actualResidentPopulation,migrant_workers=:migrantWorkers,children_proportion=:childrenProportion,young_proportion=:youngProportion,old_age_proportion=:oldAgeProportion," +
                "per_capita_income=:perCapitaIncome,main_economic_sources=:mainEconomicSources,traffic_conditions=:trafficConditions,environment=:environment,house_num=:houseNum,room_average_num=:roomAverageNum,main_building_materials=:mainBuildingMaterials,high_structure=:highStructure," +
                "steel_structure=:steelStructure,concrete_structure=:concreteStructure,masonry_structure=:masonryStructure,brick_wood_structure=:brickWoodStructure,soil_structure=:soilStructure,wood_structure=:woodStructure,stone_structure=:stoneStructure,other_structure=:otherStructure," +
                "build70s_before=:build70sBefore,build70s=:build70s,build80s=:build80s,build90s=:build90s,build00s=:build00s,build10s=:build10s,build16s=:build16s,build20s=:build20s,is_school=:isSchool,student_num=:studentNum,classroom_num=:classroomNum,is_hospital=:isHospital,doctor_num=:doctorNum,bed_num=:bedNum," +
                "is_disaster=:isDisaster,disaster_type=:disasterType,disaster_scale=:disasterScale,is_reservoir=:isReservoir,dam_type=:damType,reservoir_capacity=:reservoirCapacity,remarks=:remarks,is_submit=:isSubmit,submitter=:submitter,submitter_id=:submitterId,submit_time=:submitTime where id = '" + samplingEntity.getId() + "\'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(samplingEntity);
        log.debug(sql);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public Map<String, Object> querySamplingData(SamplingParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status " +
                "from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' and t2.del_flag = '0' ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            // whereSql.append(" and t1.township = '" + queryParams.getTownship() + "\'");
            whereSql.append(" and t1.township like '%" + queryParams.getTownship() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        if (ObjectUtils.isNotEmpty(queryParams.getTotalPopulation())) {
            whereSql.append(" and t1.total_population = '" + queryParams.getTotalPopulation() + "'");
        }
        if (ObjectUtils.isNotEmpty(queryParams.getActualResidentPopulation())) {
            whereSql.append(" and t1.actual_resident_population = '" + queryParams.getActualResidentPopulation() + "'");
        }
        if (ObjectUtils.isNotEmpty(queryParams.getMigrantWorkers())) {
            whereSql.append(" and t1.migrant_workers = '" + queryParams.getMigrantWorkers() + "'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class)).size();
        List<SamplingParams> samplingData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", samplingData);
        return map;
    }

    @Override
    public Map<String, Object> querySamplingData(SamplingParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status " +
                "from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' and t2.del_flag = '0' ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            // whereSql.append(" and t1.township = '" + queryParams.getTownship() + "\'");
            whereSql.append(" and t1.township like '%" + queryParams.getTownship() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class)).size();
        List<SamplingParams> samplingData = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", samplingData);
        return map;
    }

    @Override
    public boolean deleteSampling(String id) {
        String sql = "update ypg_sampling_survey_new set del_flag = 1  where id = ?";
        return jdbcTemplate.update(sql, id) > 0;
    }

    @Override
    public List<SamplingEntity> getDataByUserId(String taskCode, String userId) {
        //指定填写人如果等于当前登录人  可以看到      指定填写人为空   组长和组员可以看到
        String sql = "select * from ypg_sampling_survey_new where del_flag = '0' and task_code = '" + taskCode + "' and (assignor_id = '' or assignor_id = '" + userId + "\')";
        List<SamplingEntity> samplingEntities = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamplingEntity.class));
        return samplingEntities;
    }


    @Override
    public List<SamplingParams> querySamplingMap() {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT t1.id,t1.field_survey_name,t1.longitude,t1.latitude,t1.task_code,t1.is_submit,t1.assignor_name,t1.assignor_id,t1.create_id" +
                " from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' ");
        List<SamplingParams> samplingData = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        return samplingData;
    }

    @Override
    public List<SamplingEntity> getSamplingByTaskCode(String taskCode) {
        String sql = "select * from ypg_sampling_survey_new where del_flag = '0' and task_code = '" + taskCode + "'";
        List<SamplingEntity> samplingEntityList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamplingEntity.class));
        return samplingEntityList;
    }

    @Override
    public Map<String, Object> getSamplingDataByTeamMembers(SamplingParams queryParams, int curPage, int pageSize, String userId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0'  and t2.del_flag = '0' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            whereSql.append(" and t1.township like '%" + queryParams.getTownship() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class)).size();
        List<SamplingParams> buildingData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", buildingData);
        return map;
    }

    @Override
    public Map<String, Object> getSamplingDataByTeamMembers(SamplingParams queryParams, String userId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0'  and t2.del_flag = '0' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            whereSql.append(" and t1.township like '%" + queryParams.getTownship() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class)).size();
        List<SamplingParams> buildingData = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", buildingData);
        return map;
    }

    @Override
    public List<SamplingParams> getSamplingDataByTeamMembers(String userId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.assignor_name,t1.assignor_id,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num  " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0'  and t2.del_flag = '0' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.create_id = '" + userId + "'  or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "'  or t4.user_id ='" + userId + "')");
        }
        List<SamplingParams> samplingData = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        return samplingData;
    }

    @Override
    public Map<String, Object> querySamplingDataByProvince(SamplingParams queryParams, int curPage, int pageSize, String userId, String provinceId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.province_id,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num  " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            whereSql.append(" and t1.township like '%" + queryParams.getTownship() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class)).size();
        List<SamplingParams> samplingData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", samplingData);
        return map;
    }

    @Override
    public Map<String, Object> querySamplingDataByProvince(SamplingParams queryParams, String userId, String provinceId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.province_id,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num  " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            whereSql.append(" and t1.township like '%" + queryParams.getTownship() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class)).size();
        List<SamplingParams> samplingData = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", samplingData);
        return map;
    }

    @Override
    public List<SamplingParams> querySamplingDataByProvince(String userId, String provinceId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.province_id,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.assignor_name,t1.assignor_id,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num  " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        List<SamplingParams> samplingData = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        return samplingData;
    }

    @Override
    public List<SamplingEntity> querySampling(SamplingParams queryParams) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT  * from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' and t2.del_flag = '0' ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            whereSql.append(" and t1.township = '" + queryParams.getTownship() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        List<SamplingEntity> samplingData = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(SamplingEntity.class));
        return samplingData;
       /* StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status " +
                "from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' and t2.del_flag = '0' ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            // whereSql.append(" and t1.township = '" + queryParams.getTownship() + "\'");
            whereSql.append(" and t1.township like '%" + queryParams.getTownship() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        if (ObjectUtils.isNotEmpty(queryParams.getTotalPopulation())) {
            whereSql.append(" and t1.total_population = '" + queryParams.getTotalPopulation() + "'");
        }
        if (ObjectUtils.isNotEmpty(queryParams.getActualResidentPopulation())) {
            whereSql.append(" and t1.actual_resident_population = '" + queryParams.getActualResidentPopulation() + "'");
        }
        if (ObjectUtils.isNotEmpty(queryParams.getMigrantWorkers())) {
            whereSql.append(" and t1.migrant_workers = '" + queryParams.getMigrantWorkers() + "'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        sql.append(whereSql).append(orderSql);
        List<SamplingEntity> samplingData = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingEntity.class));
     //   List<SamplingEntity> samplingData = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(SamplingEntity.class));
        return samplingData;*/
    }

    @Override
    public List<SamplingEntity> querySamplingByProvince(SamplingParams queryParams, String userId, String provinceId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder("SELECT t1.* from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num  " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            whereSql.append(" and t1.township = '" + queryParams.getTownship() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        List<SamplingEntity> samplingEntities = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(SamplingEntity.class));
        return samplingEntities;
      /*  StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.province_id,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num  " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            whereSql.append(" and t1.township like '%" + queryParams.getTownship() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        sql.append(whereSql).append(orderSql);
        List<SamplingEntity> samplingData = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingEntity.class));
        return samplingData;*/
    }

    @Override
    public List<SamplingEntity> querySamplingDataByTeamMembers(SamplingParams queryParams, String userId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder("SELECT t1.* from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num  " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' and t2.del_flag = '0' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            whereSql.append(" and t1.township = '" + queryParams.getTownship() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        List<SamplingEntity> samplingData = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(SamplingEntity.class));
        return samplingData;

       /* StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.create_id,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_sampling_survey_new t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0'  and t2.del_flag = '0' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownship())) {
            whereSql.append(" and t1.township like '%" + queryParams.getTownship() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())) {
            whereSql.append(" and t1.township_id = '" + queryParams.getTownshipId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())) {
            whereSql.append(" and t1.field_survey_name like '%" + queryParams.getFieldSurveyName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())) {
            whereSql.append(" and t1.is_submit = '" + queryParams.getIsSubmit() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        sql.append(whereSql).append(orderSql);
        List<SamplingEntity> buildingData = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingEntity.class));
        return buildingData;*/

    }

    @Override
    public SamplingEntity getNearestSamplingAPP(String lonlat, String taskCode) {
        String sql = "select * from ypg_sampling_survey_new where del_flag = '0' and task_code = '" + taskCode + "' ORDER BY st_distance(geom,st_geometryfromtext('POINT(" + lonlat + ")',4490)) LIMIT 1;";
        try {
            SamplingEntity samplingEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SamplingEntity.class));
            return samplingEntity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public String getGeomByCode(String areaCode) {
        try {
            String sql = "select ST_AsText(geom) geom from district_boundary_county where code = ?";
            String geom = jdbcTemplate.queryForObject(sql, String.class, areaCode);
            return geom;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<Map<String, Object>> getFieldSurveyNameByTaskCode(String taskCode) {
        String sql = "select id,field_survey_name from ypg_sampling_survey_new where del_flag='0' and task_code = ?";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, taskCode);
        return list;
    }

    @Override
    public List<SamplingEntity> getSamplingEntityList(List<String> taskNumList) {
//        String sql = "select * from ypg_sampling_survey_new where task_code in (select distinct task_num from ypg_survey_task where parent_task='" + taskCode + "' or task_num='" + taskCode + "') and del_flag='0' ORDER BY province_id,city_id,area_id";
//        List<SamplingEntity> samplingData = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamplingEntity.class));
//        return samplingData;
        String sql = "select * from ypg_sampling_survey_new where task_code in (:taskNumList) and del_flag='0'";
        Map idMap = new HashMap();
        idMap.put("taskNumList", taskNumList);
        return namedParameterJdbcTemplate.query(sql, idMap, new BeanPropertyRowMapper<>(SamplingEntity.class));
    }

    @Override
    public boolean saveStructureFeature(StructureFeature structureFeature) {
        String sql = "update ypg_sampling_survey_new set high_feature=:highFeature,steel_feature=:steelFeature,concrete_feature=:concreteFeature,masonry_feature=:masonryFeature,brick_wood_feature=:brickWoodFeature,soil_feature=:soilFeature,wood_feature=:woodFeature,stone_feature=:stoneFeature,other_feature=:otherFeature"
                + " where id = '" + structureFeature.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(structureFeature);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public HashMap<String, Object> getSamplingSurveyPoint(SamplingSurveyPointVo samplingSurveyPointVo, int page, int size) {
        StringBuilder sql = new StringBuilder("select * from ypg_sampling_survey_new where del_flag='0' and province_id='" + samplingSurveyPointVo.getProvince() + "'");
        if (!PlatformObjectUtils.isEmpty(samplingSurveyPointVo.getCity())) {
            sql.append(" and city_id='" + samplingSurveyPointVo.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(samplingSurveyPointVo.getCounty())) {
            sql.append(" and area_id='" + samplingSurveyPointVo.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(samplingSurveyPointVo.getTownship())) {
            sql.append(" and township like '%" + samplingSurveyPointVo.getTownship() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(samplingSurveyPointVo.getSurveyPointName())) {
            sql.append(" and field_survey_name like '%" + samplingSurveyPointVo.getSurveyPointName() + "%'");
        }
        //StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        //sql.append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sql.append(limitSql);
        List<SamplingParams> samplingSurveyPointVoList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", samplingSurveyPointVoList);
        if (!PlatformObjectUtils.isEmpty(samplingSurveyPointVo.getSamplingSurveyPointId())) {
            ArrayList<SamplingParams> associatedDataList = new ArrayList<>();
            String[] surveyPointIds = samplingSurveyPointVo.getSamplingSurveyPointId().split(",");
            for (String surveyPointId : surveyPointIds) {
                StringBuilder associatedDataSql = new StringBuilder("select * from ypg_sampling_survey_new where id='" + surveyPointId + "'");
                SamplingParams samplingParams = jdbcTemplate.queryForObject(associatedDataSql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
                associatedDataList.add(samplingParams);
            }
            resultMap.put("associatedData", associatedDataList);
        }
        return resultMap;
    }

    @Override
    public List<SamplingEntity> getSamplingListByTaskId(String taskId) {
        String sql = "select * from ypg_sampling_survey_new where  (select sampling_survey_point_id from ypg_preassess_task where id='" + taskId + "') like '%' || ID || '%' and del_flag='0'";
        List<SamplingEntity> samplingData = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamplingEntity.class));
        return samplingData;
    }

    @Override
    public String getFieldSurveyId(String taskCode, String fieldSurveyName) {
        try {
            String sql = "select id from ypg_sampling_survey_new where del_flag='0' and task_code = ? and field_survey_name = ?";
            String id = jdbcTemplate.queryForObject(sql, String.class, taskCode, fieldSurveyName);
            return id;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public SamplingEntity getSamplingEntityList(String taskCode, String surveyName, String province, String city, String county) {
        StringBuilder sql = new StringBuilder("select * from ypg_sampling_survey_new where del_flag='0' and task_code ='" + taskCode + "'");
        sql.append(" and field_survey_name='" + surveyName + "' and province='" + province + "' and city='" + city + "'");
        if (!"".equals(county)) {
            sql.append(" and area='" + county + "'");
        }
        return jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(SamplingEntity.class));
    }

    @Override
    public void update(String id, String typeImageFileId, String yearsImageFileId) {
        String sql = "update ypg_sampling_survey_new set type_pie_id ='" + typeImageFileId + "'," +
                "years_pie_id ='" + yearsImageFileId + "' where id = '" + id + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public boolean existByFieldSurveyNameAndTaskCode(String fieldSurveyName, String taskCode) {
        String sql = "select count(*) from ypg_sampling_survey_new where field_survey_name ='" + fieldSurveyName + "' and task_code = '" + taskCode + "' and del_flag = '0'";
        return jdbcTemplate.queryForObject(sql, Integer.class) != 0;
    }

    @Override
    public List<SamplingEntity> getByTaskCodeAndProvinceId(String taskNum, String provinceId) {
        String sql = String.format("select count(*) from ypg_sampling_survey_new where task_code = '%s' and province_id = '%s'  and del_flag='0' ", taskNum, provinceId);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamplingEntity.class));
    }

    @Override
    public Integer countByTaskCodeAndProvinceId(String taskNum, String provinceId) {
        String sql = String.format("select count(*) from ypg_sampling_survey_new where task_code = '%s' and province_id = '%s' and del_flag='0' ", taskNum, provinceId);
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public Integer countCommitByTaskCodeAndProvinceId(String taskNum, String provinceId) {
        String sql = String.format("select count(*) from ypg_sampling_survey_new where task_code = '%s' and province_id = '%s' and is_submit = '1' and del_flag='0' ", taskNum, provinceId);
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public Integer countByAreaNameAndTask(String taskNum, String p, String city, String c, String v) {
        String sql = String.format("select count(*) from ypg_sampling_survey_new where task_code = '%s' and province = '%s' and city='%s' and area ='%s' and township = '%s'  and del_flag='0' ", taskNum, p, city, c, v);
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public List<SamplingEntity> getByArea(String province, String city, String area) {
        String sql = String.format("select * from ypg_sampling_survey_new where province='"+province+"' and city ='"+city+"' and area='"+area+"' and del_flag='0'");
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamplingEntity.class));
    }
}
